﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;
using DropNet;
using DropNet.Exceptions;
using ENT.PM.Ws.source;
using Excel;

namespace ENT.PM.Ws.source
{
    /// <summary>
    /// 
    /// </summary>
    public class ExcelData
    {
        public string Mes;
        public float Ingreso;
        public float Egreso;
        public float Saldo;

        float IngresoTotal;
        float EgresoTotal;
        float SaldoTotal;

        public ExcelData()
        {
            this.Mes = "";
            this.Ingreso = 0;
            this.Egreso = 0;
            this.Saldo = 0;
            this.IngresoTotal = 0;
            this.EgresoTotal = 0;
            this.SaldoTotal = 0;
        }

        public ExcelData(string _mes)
        {

            this.Mes = _mes;
            this.Ingreso = 0;
            this.Egreso = 0;
            this.Saldo = 0;
            this.IngresoTotal = 0;
            this.EgresoTotal = 0;
            this.SaldoTotal = 0;
        }

        internal void CompleteInfo(string _ingreso, string _egreso, string _saldo)
        {
            float.TryParse(_ingreso, out this.Ingreso);
            float.TryParse(_egreso, out this.Egreso);
            float.TryParse(_saldo, out this.Saldo);
           
        }
        internal void CompleteInfo()
        {
            this.Ingreso    =this.IngresoTotal;
            this.Egreso     =this.EgresoTotal;
            this.Saldo      =this.SaldoTotal;
        }
       

        internal void SetSummary(ExcelData rowInfo)
        {
            this.IngresoTotal += rowInfo.Ingreso;
            this.EgresoTotal += rowInfo.Egreso;
            
        }

        internal ExcelData GetSummary()
        {
           this.Ingreso = this.IngresoTotal;
           this.Egreso  = this.EgresoTotal;
           this.Saldo   = this.Ingreso - this.Egreso;
           return (this);
        }
    }
    public class Info
    {
      
        public List<ExcelData> listExcel { get; set; }
        public string seek { get; set; }
        public string msg { get; set; }
        public  Info()
        {
            listExcel = new List<ExcelData>();
            seek = "false";
        }
   
    }

    public class RootObject
    {
        public Info info { get; set; }
        public RootObject()
        {
            info= new Info();
        }
     
    }
    static class DrobBoxDataExcel
    {
        public static RootObject GetData(string anyo, string mes,Boolean bTotaliceEnd=false) 
        {
            RootObject rtaObject = new RootObject();
            string stringResultData = "";
            try
            {
                //Leer archivo de dropbox
                var fileInfo = new byte[1];
                try
                {
                    var _client = new DropNetClient(KeyDropbox.ApiKey, KeyDropbox.ApiSecret, KeyDropbox.Token, KeyDropbox.Secret);
                    _client.UseSandbox = true;
                    fileInfo = _client.GetFile(KeyDropbox.Folder + KeyDropbox.File);
                }
                catch (DropboxException exDb)
                {

                    throw exDb;
                }

                if (fileInfo.Length > 1)
                    using (MemoryStream memStream = new MemoryStream(fileInfo))
                    {
                        
                        //1. Reading from a binary Excel file ('97-2003 format; *.xls)
                        //IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                        //...
                        //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                        //var excelReader = ExcelReaderFactory.CreateBinaryReader((Stream)memStream);
                        var excelReader = ExcelReaderFactory.CreateOpenXmlReader((Stream)memStream);
                        //...
                        //3. DataSet - The result of each spreadsheet will be created in the result.Tables
                        DataSet result = excelReader.AsDataSet();

                      
                        if (result!=null && result.Tables.Contains(anyo))
                        {

                            if (mes.Equals(""))
                            {
                               
                                ExcelData rowDataTmpSum = new ExcelData("Total");
                                foreach (DataRow dr in result.Tables[anyo].Rows)
                                {
                                    ExcelData rowDataTmp1 = new ExcelData(dr[0].ToString());
                                    rowDataTmp1.CompleteInfo(dr[1].ToString(), dr[2].ToString(), dr[3].ToString());
                                    rtaObject.info.listExcel.Add(rowDataTmp1);
                                    rowDataTmpSum.SetSummary(rowDataTmp1);
                                }
                                if (result.Tables[anyo].Rows.Count!=0)
                                {
                                    if (bTotaliceEnd)
                                    {
                                        rtaObject.info.listExcel.Remove(rtaObject.info.listExcel[0]);
                                        rtaObject.info.listExcel.Add(rowDataTmpSum.GetSummary());
                                    }
                                    else
                                    {
                                        rtaObject.info.listExcel[0]=rowDataTmpSum.GetSummary();
                                    }
                                   
                                
                                }
                            }
                            else
                            {
                                
                                foreach (DataRow dr in result.Tables[anyo].Rows)
                                {
                                    ExcelData rowDataTmp2 = new ExcelData(dr[0].ToString());
                                    if (mes.ToUpper() == rowDataTmp2.Mes.ToUpper())
                                    {
                                        rowDataTmp2.CompleteInfo(dr[1].ToString(), dr[2].ToString(), dr[3].ToString());
                                        rtaObject.info.listExcel.Add(rowDataTmp2);
                                      
                                        break;
                                    }

                                }
                              
                            }

                        }
                        //6. Free resources (IExcelDataReader is IDisposable)
                        excelReader.Close();

                    }
               
                rtaObject.info.msg = "";
                rtaObject.info.seek = "true";
               
            }
            catch (Exception ex)
            {

                string exp = ex.Message.Replace("\"", "-");
                exp = exp.Replace("'", "-");
                rtaObject.info.msg  = exp;
                rtaObject.info.seek = "false";
                rtaObject.info.listExcel = new List<ExcelData>();
                
            }
            //Context.Response.Write(stringResultData);
            return (rtaObject);
        }

    }


    
}